package cn.com.dashihui.web.service;

import java.util.ArrayList;
import java.util.List;

import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

import cn.com.dashihui.web.dao.GoodsStore;
import cn.com.dashihui.web.dao.Store;
import cn.com.dashihui.web.dao.User;

public class EcardOrderService {

	/**
	 * 分页查找订单信息
	 * @param orderNum 订单编号
	 * @param beginDate 下单时间
	 * @param endDate 成交时间
	 * @param state 订单状态
	 * @param address 收货地址
	 * @param tel 买家电话
	 * @param payType 支付方式
	 */
	public Page<Record> findByPage(int pageNum,int pageSize,String orderNum,String beginDate,String endDate,int state,String sellerId,String tel,String storeid){
		StringBuffer sBuffer = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		sBuffer.append(" FROM t_bus_order_ecard A "
				+ "inner JOIN t_bus_user F on A.userid=F.id "
				+ "LEFT JOIN t_dict_store B ON F.toBossTwoId = B.id "
				+ "LEFT JOIN t_bus_seller C on F.toBossThreeId=C.id "
				+ "LEFT JOIN t_bus_user G on F.toBossOneId=G.id "
				+ "LEFT JOIN t_bus_user_wx D on A.userid=D.id where 1=1 ");
		if(!StrKit.isBlank(sellerId)&& !"0".equals(sellerId)){
			sBuffer.append(" AND C.sellerId in ('"+sellerId+"')");
		}
		if(!StrKit.isBlank(storeid)&&!"0".equals(storeid)){
			sBuffer.append(" AND B.id in ('"+storeid+"')");
		}

		if(!StrKit.isBlank(orderNum)){
			sBuffer.append(" AND A.orderNum=?");
			params.add(orderNum);
		}
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d') BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		switch (state) {
		case 1:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 2:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 3:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 4:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 5:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 6:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		}
		if(!StrKit.isBlank(tel)){
			sBuffer.append(" AND A.tel=?");
			params.add(tel);
		}
			sBuffer.append(" AND A.paytype!=6");
			sBuffer.append(" ORDER BY A.startDate DESC");
		//查询出符合条件的订单列表
		Page<Record> page = Db.paginate(pageNum,pageSize,"SELECT A.*,C.sellerName,B.title as xdstore,F.id as uid,F.name,F.card,F.phone,F.toBossOneId,G.name as jjrName ",sBuffer.toString(),params.toArray());
		return page;
	}
	/**
	 * 分页查找当前用户的充值订单信息
	 * @param orderNum 订单编号
	 * @param beginDate 下单时间
	 * @param endDate 成交时间
	 * @param state 订单状态
	 * @param address 收货地址
	 * @param tel 买家电话
	 * @param payType 支付方式
	 */
	public Page<Record> findByPageRecharge(int pageNum,int pageSize,String orderNum,String beginDate,String endDate,int state,String sellerId,String tel,String storeid,int userId){
		StringBuffer sBuffer = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		sBuffer.append(" FROM t_bus_order_ecard A "
				+ "inner JOIN t_bus_user F on A.userid=F.id "
				+ "LEFT JOIN t_dict_store B ON F.toBossTwoId = B.id "
				+ "LEFT JOIN t_bus_seller C on F.toBossThreeId=C.id "
				+ "LEFT JOIN t_bus_user G on F.toBossOneId=G.id "
				+ "LEFT JOIN t_bus_user_wx D on A.userid=D.id where 1=1 ");
		if(!StrKit.isBlank(sellerId)&& !"0".equals(sellerId)){
			sBuffer.append(" AND C.sellerId in ('"+sellerId+"')");
		}
		if(!StrKit.isBlank(storeid)&&!"0".equals(storeid)){
			sBuffer.append(" AND B.id in ('"+storeid+"')");
		}

		if(!StrKit.isBlank(orderNum)){
			sBuffer.append(" AND A.orderNum=?");
			params.add(orderNum);
		}
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d') BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		switch (state) {
		case 1:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 2:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 3:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 4:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 5:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 6:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		}
		if(!StrKit.isBlank(tel)){
			sBuffer.append(" AND A.tel=?");
			params.add(tel);
		}
		if(userId != 0){
			sBuffer.append(" AND A.userid =? ");
			params.add(userId);
		}
			sBuffer.append(" AND A.paytype!=6");
			sBuffer.append(" ORDER BY A.startDate DESC");
		//查询出符合条件的订单列表
		Page<Record> page = Db.paginate(pageNum,pageSize,"SELECT A.*,C.sellerName,B.title as xdstore,F.id as uid,F.name,F.card,F.phone,F.toBossOneId,G.name as jjrName ",sBuffer.toString(),params.toArray());
		return page;
	}
	/**
	 * 分页查找当前用户的提现订单信息
	 * @param orderNum 订单编号
	 * @param beginDate 下单时间
	 * @param endDate 成交时间
	 * @param state 订单状态
	 * @param address 收货地址
	 * @param tel 买家电话
	 * @param payType 支付方式
	 */
	public Page<Record> findByPageExtract(int pageNum,int pageSize,String orderNum,String beginDate,String endDate,int state,String sellerId,String tel,String storeid,int userId){
		StringBuffer sBuffer = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		sBuffer.append(" FROM t_bus_order_ecard A "
				+ "inner JOIN t_bus_user F on A.userid=F.id "
				+ "LEFT JOIN t_dict_store B ON F.toBossTwoId = B.id "
				+ "LEFT JOIN t_bus_seller C on F.toBossThreeId=C.id "
				+ "LEFT JOIN t_bus_user G on F.toBossOneId=G.id "
				+ "LEFT JOIN t_bus_user_card H on H.userid=F.id "
				+ "LEFT JOIN t_bus_user_wx D on A.userid=D.id where 1=1 ");
		if(!StrKit.isBlank(sellerId)&& !"0".equals(sellerId)){
			sBuffer.append(" AND C.sellerId in ('"+sellerId+"')");
		}
		if(!StrKit.isBlank(storeid)&&!"0".equals(storeid)){
			sBuffer.append(" AND B.id in ('"+storeid+"')");
		}

		if(!StrKit.isBlank(orderNum)){
			sBuffer.append(" AND A.orderNum=?");
			params.add(orderNum);
		}
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d') BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		switch (state) {
		case 1:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 2:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 3:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 4:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 5:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 6:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		}
		if(!StrKit.isBlank(tel)){
			sBuffer.append(" AND A.tel=?");
			params.add(tel);
		}
		if(userId != 0){
			sBuffer.append(" AND A.userid =? ");
			params.add(userId);
		}
			sBuffer.append(" AND A.paytype=6");
			sBuffer.append(" ORDER BY A.startDate DESC");
		//查询出符合条件的订单列表
		Page<Record> page = Db.paginate(pageNum,pageSize,"SELECT A.*,C.sellerName,B.title as xdstore,F.id as uid,F.name,F.card,F.phone,F.toBossOneId,G.name as jjrName,H.cardNo as bankCardNo ",sBuffer.toString(),params.toArray());
		return page;
	}
	/**
	 * 查询所需积分”总和，“现金价格”总和,退款价格总和，实际收入
	 */

	public Record findTotal(String beginDate,String endDate,String storeid,String tel,String orderNum,int state,int seid,int userid){
		StringBuffer sBuffer = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		sBuffer.append("SELECT IFNULL(ROUND(SUM(amount), 2),0.00) amount,"
				+ "IFNULL(SUM(payPoint),0) payPoint,"
				+ "IFNULL(ROUND(SUM(amount1), 2),0.00) tuikuan1,"
				+ "IFNULL(ROUND(SUM(amount2), 2),0.00) duihuan,"
				+ "IFNULL(ROUND(SUM(amount3), 2),0.00) nduihuan ,"
				+ "ROUND(IFNULL(SUM(amount3),0.00), 2) as shiji  "
				+ "from ( select case when  A.payState=2 AND (A.orderState=1 or A.orderState=3) and A.tuistate=2 then A.amount else 0 end as amount1,"
				+ "case when A.payState=2 AND A.orderState=2 then A.amount else 0.00 end as amount2,"
				+ "case when A.orderState!=1 and A.orderState!=5 then A.amount else 0.00 end as amount3,"
				+ "case when A.orderState!=5 then A.payPoint else 0.00 end as payPoint,A.amount FROM t_bus_order_ecard A left join t_bus_user B on B.id = A.userid left join t_bus_goods_ecard tg on A.goodsid = tg.id WHERE 1=1 ");
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d') BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		if(seid != 0){
			sBuffer.append(" AND B.toBossTwoId in ('"+seid+"')");
		}
		if(userid != 0){
			sBuffer.append(" AND A.userid in ('"+userid+"')");
		}
		if(!StrKit.isBlank(tel)){
			sBuffer.append(" AND A.tel=?");
			params.add(tel);
		}
		if(!StrKit.isBlank(orderNum)){
			sBuffer.append(" AND A.orderNum=?");
			params.add(orderNum);
		}
		switch (state) {
		case 1:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 2:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 3:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 4:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 5:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 6:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		}
		sBuffer.append(" ) a ");
		Record record =Db.findFirst(sBuffer.toString(),params.toArray());
		return record;	
	}
	/**
	 * 统计总提现金额,驳回金额，通过金额
	 */
	public Record findTotalMoney(String beginDate,String endDate,String storeid,String tel,String name,String orderNum,int state,int seid,int userid){
		StringBuffer sBuffer = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		sBuffer.append("SELECT IFNULL(ROUND(SUM(amount0), 2),0.00) amount,"
				+ "IFNULL(ROUND(SUM(amount1), 2),0.00) bohui,"
				+ "IFNULL(ROUND(SUM(amount2), 2),0.00) tongguo,"
				+ "IFNULL(ROUND(SUM(amount3), 2),0.00) daishenhe "
				+ "from ( select "
				+ "case when A.outStarte!=0 then A.amount else 0 end as amount0, "
				+ "case when A.outStarte=1 then A.amount else 0 end as amount3, "
				+ "case when A.outStarte=3 then A.amount else 0 end as amount1, "
				+ "case when (A.outStarte=2 or A.outStarte=4) then A.amount else 0.00 end as amount2 "
				+ "FROM t_bus_order_ecard A "
				+ "left join t_bus_user B on B.id = A.userid "
				+ "left join t_bus_goods_ecard tg on A.goodsid = tg.id "
				+ "WHERE 1=1 AND A.paytype=6 ");
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d') BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		if(seid != 0){
			sBuffer.append(" AND B.toBossTwoId in ('"+seid+"')");
		}
		if(userid != 0){
			sBuffer.append(" AND A.userid in ('"+userid+"')");
		}
		if(StrKit.notBlank(tel)){
			sBuffer.append(" AND B.phone like ?");
			params.add("%"+tel+"%");
		}
		if(StrKit.notBlank(name)){
			sBuffer.append(" AND B.name like ?");
			params.add("%"+name+"%");
		}
		if(!StrKit.isBlank(orderNum)){
			sBuffer.append(" AND A.orderNum=?");
			params.add(orderNum);
		}
		switch (state) {
		case 1:
			sBuffer.append(" AND A.outStarte=?");
			params.add(state);
			break;
		case 2:
			sBuffer.append(" AND A.outStarte=?");
			params.add(state);
			break;
		case 3:
			sBuffer.append(" AND A.outStarte=?");
			params.add(state);
			break;
		case 4:
			sBuffer.append(" AND A.outStarte=?");
			params.add(state);
			break;
		}
		sBuffer.append(" ) a ");
		Record record =Db.findFirst(sBuffer.toString(),params.toArray());
		return record;	
	}
	//查询订单
	public Page<Record> goodsOrder(int pageNum, int pageSize, String orderNum, String beginDate, String endDate, int state,int seid, String tel, int stid, int id) {
		StringBuffer sBuffer = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		sBuffer.append(" FROM t_bus_order_ecard A "
				+ "inner JOIN t_bus_user F on A.userid=F.id "
				+ "LEFT JOIN t_dict_store B ON F.toBossTwoId = B.id "
				+ "LEFT JOIN t_bus_seller C on F.toBossThreeId=C.id "
				+ "LEFT JOIN t_bus_user_wx D on A.userid=D.id "
				+ "LEFT JOIN t_bus_order_ecard_toker_user_log L ON A.orderNum=L.orderNum "
				+ "LEFT JOIN t_dict_store E ON A.storeid = E.storeid where 1=1  and A.goodsid=?");
		params.add(id);
		if(seid != 0){
			sBuffer.append(" AND F.toBossThreeId=? ");
			params.add(seid);
		}
		if(stid != 0){
			sBuffer.append(" AND F.toBossTwoId=? ");
			params.add(stid);
		}
		if(!StrKit.isBlank(orderNum)){
			sBuffer.append(" AND A.orderNum=?");
			params.add(orderNum);
		}
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND A.startDate BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(A.startDate,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		switch (state) {
		case 1:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 2:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 3:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 4:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 5:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		case 6:
			sBuffer.append(" AND A.orderState=?");
			params.add(state);
			break;
		}
		 /*
			default:
				//默认支付方式
				sBuffer.append(" AND A.state!=1");
				break;*/
		if(!StrKit.isBlank(tel)){
			sBuffer.append(" AND A.tel=?");
			params.add(tel);
		}
		
		sBuffer.append(" ORDER BY A.startDate DESC");
		//查询出符合条件的订单列表
		Page<Record> page = Db.paginate(pageNum,pageSize,"SELECT A.*,B.title as xdstore,F.id as uid,F.name,F.card,F.toBossOneId,E.title as dhstore,C.legalPerson as jjrName,L.totalPoint,L.totalDetion,L.totalDcount,L.id as tokerid",sBuffer.toString(),params.toArray());
		return page;
	}

	/********************************************会员
	 * 查询用户信息
	 * @param id
	 * @return
	 */
	public User findById(int id){
		System.out.println(id);
		return User.me().findFirst("SELECT A.* ,ifnull(C.countcum,0) countnum ,ifnull(C.allCount,0) allCount, "
				+ " (year(now())-year(A.birthday)-1) + ( DATE_FORMAT(A.birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') ) as age "
				+ " FROM t_bus_user A left join t_bus_user_wx B on A.id=B.userid left join (select userid,count(id) as countcum,sum(amount) as allCount from "
				+ " t_bus_order_ecard where orderState=2 group by userid) C on C.userid= B.id WHERE A.id=?",id);
	}
	/** ************************************************会员
	 * 查询分公司信息
	 * @return
	 */
	public List<Store> findStoreList() {
		return Store.me().find("select storeid,title from t_dict_store");
	}
	/** **************************************************会员
	 * 查询真实名字
	 * @param store
	 * @return
	 */
	public List<Record> findUserList(String store) {
		return Db.find("select trueName from t_auth_admin_seller A left join t_dict_store B on A.storeid=B.storeid where B.title=?",store);
	}

	/**
	 * 分页查找订单信息
	 * @param orderNum 订单编号
	 * @param beginDate 下单时间
	 * @param endDate 成交时间
	 * @param state 订单状态
	 * @param address 收货地址
	 * @param tel 买家电话
	 * @param payType 支付方式
	 */
	public Page<Record> findUserOrder(int pageNum,int pageSize,int userId){
			String sql=" FROM t_bus_order_ecard A "
					+ "left join t_bus_user_wx B on B.id=A.userid "
					+ "left join t_bus_user C on C.id=A.userid "
					+ "left join t_dict_store D on D.id=C.toBossTwoId "
					+ "left join t_bus_seller F on F.id=C.toBossThreeId "
					+ " where A.payState=2 AND C.id="+userId +" ORDER BY A.startDate DESC ";
			return Db.paginate(pageNum, pageSize,"SELECT A.*,C.*,D.title as storeName,F.sellerName ",sql);
	}

	public User findUser(int id) {
		// TODO Auto-generated method stub
		return User.me().findFirst("SELECT * FROM t_bus_user WHERE id=?",id);
	}
	public List<GoodsStore> findGoods() {
		// TODO Auto-generated method stub
		return GoodsStore.me().find("select id,name from t_bus_goods_ecard where groupbuying = 2");

	}
}
